<?php
/*
require(PATH.'/inc/plugins/fpdf/fpdf.php');
*/

class Clients extends Table
{		
    // Ecrit le tableau des clients d'un utilisateur
    public static function Tableau_uti($db, $uti_id)//--------------------------
    {
        $sql="SELECT cli_uti_id, DATE_FORMAT(cli_annee, '%m/%Y') as cli_annee, cli_id, cli_nom, bra_id, bra_code, bra_code as cli_bra_code, bra_nom, zon_id, zon_code as cli_zon_code, zon_code, zon_nom, cli_ville, count(mac_id) as cli_mac_nb".
            ' FROM utilisateurs'.
            ' JOIN clients ON clients.cli_uti_id = utilisateurs.uti_id'.
            ' JOIN branches ON clients.cli_bra_id = branches.bra_id'.
            ' JOIN zones ON clients.cli_zon_id = zones.zon_id'.
            ' LEFT JOIN machines ON clients.cli_id = machines.mac_cli_id'.
            " GROUP BY cli_id HAVING cli_uti_id=$uti_id";
        $champs = array ('cli_nom', 'cli_annee', 'cli_bra_code', 'cli_zon_code', 'cli_ville', 'cli_mac_nb');
        $msg_vide = LNG_CLI_VIDE;
		return parent::Tableau($db, $sql, $champs, $champs, $msg_vide, true);
    }//------------------------------------------------------------------Tableau


    // Ecrit le tableau des clients d'une branche
    public static function Tableau_bra($db, $bra_id)//--------------------------
    {
        $sql="SELECT cli_bra_id, cli_id, cli_nom, uti_id, uti_code, CONCAT_WS(', ',uti_nom,uti_prenom) as uti_nom, bra_id, bra_code, bra_code as cli_bra_code, zon_id, zon_code, zon_code as cli_zon_code, zon_nom, cli_ville, count(mac_id) as cli_mac_nb ".
            ' FROM utilisateurs '.
            ' JOIN clients ON clients.cli_uti_id = utilisateurs.uti_id'.
            ' JOIN branches ON clients.cli_bra_id = branches.bra_id'.
            ' JOIN zones ON clients.cli_zon_id = zones.zon_id '.
            ' LEFT JOIN machines ON clients.cli_id = machines.mac_cli_id '.
            " GROUP BY cli_id HAVING cli_bra_id=$bra_id";
        $champs = array ('cli_nom', 'cli_bra_code', 'cli_zon_code', 'cli_ville', 'cli_mac_nb');
        $msg_vide = LNG_CLI_VIDE;
		return parent::Tableau($db, $sql, $champs, $champs, $msg_vide, true);
	}


    // Ecrit le tableau des clients d'une zone
    public static function Tableau_zon($db, $zon_id)//--------------------------
    {
        $sql="SELECT zon_code, zon_code as cli_zon_code, zon_id, bra_id, bra_code, bra_code as cli_bra_code, cli_zon_id, cli_id, cli_nom, uti_id, CONCAT_WS(', ',uti_nom,uti_prenom) as uti_nom, bra_id, bra_nom, cli_ville, count(mac_id) as cli_mac_nb ".
            ' FROM utilisateurs '.
            ' JOIN clients ON clients.cli_uti_id = utilisateurs.uti_id'.
            ' JOIN branches ON clients.cli_bra_id = branches.bra_id'.
            ' JOIN zones ON clients.cli_zon_id = zones.zon_id '.
            ' LEFT JOIN machines ON clients.cli_id = machines.mac_cli_id '.
            " GROUP BY cli_id HAVING cli_zon_id=$zon_id";
        $champs = array ('cli_nom', 'cli_bra_code', 'cli_zon_code', 'cli_ville', 'cli_mac_nb');
        $msg_vide = LNG_CLI_VIDE;
		return parent::Tableau($db, $sql, $champs, $champs, $msg_vide, True);
	}

    public static function Tableau($db, $cli_nom, $cli_zon_id, $cli_bra_id, $cli_uti_id, $mod_id)
    {
		function moinsunversrien($var)
		{
			return ($var==-1)?'':$var;
		}
		$cli_zon_id = moinsunversrien($cli_zon_id);
		$cli_bra_id = moinsunversrien($cli_bra_id);
		$cli_uti_id = moinsunversrien($cli_uti_id);
        $sql="SELECT cli_id, cli_nom, bra_id, bra_code, bra_nom, zon_id, zon_code, zon_nom, uti_id, uti_code, uti_nom, uti_prenom, cli_ville, COUNT(mac_id) as cli_mac_nb, mac_mod_id".
            ' FROM utilisateurs '.
            ' JOIN clients ON clients.cli_uti_id = utilisateurs.uti_id'.
            ' JOIN branches ON clients.cli_bra_id = branches.bra_id'.
            ' JOIN zones ON clients.cli_zon_id = zones.zon_id '.
            ' LEFT JOIN machines ON clients.cli_id = machines.mac_cli_id '.
            " GROUP BY cli_id";
        $having = array();
        if ( !empty($cli_nom) )
			$having[] = "cli_nom LIKE '%$cli_nom%'";
        if ( !empty($cli_zon_id) )
			$having[] = "zon_id = $cli_zon_id";
        if ( !empty($cli_bra_id) )
			$having[] = "bra_id = $cli_bra_id";
        if ( !empty($cli_uti_id) )
			$having[] = "uti_id = $cli_uti_id";
/*
		if ( !empty($mod_id) )
			$having[] = "mac_mod_id = $mod_id";
*/
		if ( sizeof($having)>0 ) 
		{
			$sql .= ' HAVING '.implode(' AND ', $having);
		}
        //$sql.=' ORDER BY cli_nom ASC';
        $champs = array ('cli_nom', 'cli_bra_code', 'cli_zon_code', 'cli_ville', 'cli_mac_nb');
        $champs_tri = array ('cli_nom', 'bra_code', 'zon_code', 'cli_ville', 'cli_mac_nb');
        $msg_vide = LNG_CLI_VIDE;
		return parent::Tableau($db, $sql, $champs, $champs_tri, $msg_vide,true);
	}

    // Ecrit le tableau des clients d'un utilisateur
    public static function Tableau_rapide($db, $critere)//--------------------------
    {
        $sql='SELECT cli_uti_id, cli_id, cli_nom, bra_id, bra_code, bra_nom, zon_id, zon_code, zon_nom, cli_ville, count(mac_id) as cli_mac_nb'.
            ' FROM utilisateurs'.
            ' JOIN clients ON clients.cli_uti_id = utilisateurs.uti_id'.
            ' JOIN branches ON clients.cli_bra_id = branches.bra_id'.
            ' JOIN zones ON clients.cli_zon_id = zones.zon_id'.
            ' LEFT JOIN machines ON clients.cli_id = machines.mac_cli_id'.
            " GROUP BY cli_id".
            " HAVING cli_ville LIKE '%$critere%' OR cli_nom LIKE '%$critere%'".
            ' ORDER BY cli_nom ASC';
        $champs = array ('cli_nom', 'cli_bra_code', 'cli_zon_code', 'cli_ville', 'cli_mac_nb');
        $msg_vide = LNG_CLI_VIDE;
		return parent::Tableau($db, $sql, $champs, $champs, $msg_vide);
    }//------------------------------------------------------------------Tableau
	
    public static function Select($db, $idexclu)
    {
        $sql="SELECT cli_id, cli_nom".
            ' FROM clients'.
            " WHERE cli_id!=$idexclu".
            ' ORDER BY cli_nom ASC';
        if ($result = $db->query($sql))
		{
			if ( $result->num_rows<1 )
				return False;
			$retour = Array();
			while ( $row = $result->fetch_assoc() )
				$retour[] = Array (	$row['cli_id'],
									$row['cli_nom']);
			return $retour;
		}
		return False;
    }
    
    public static function Access2Id($db,$code)
    {
		$sql="SELECT cli_id, cli_codeaccess FROM clients WHERE cli_accesscode='$code'";
        if ($result = $db->query($sql))
		{
			if ( $result->num_rows<1 )
				return False;
			$row = $result->fetch_assoc();
			return $row['cli_id'];
		}
		return False;
	}

	public static function UpdateUti($db, $ancien,$nouveau)
	{
		$sql = "UPDATE clients SET cli_uti_id='$nouveau' WHERE cli_uti_id='$ancien'";
        if ( $result = $db->query($sql) )
		{
			return True;
		}
		return False;
	}
	

	public static function UpdateBra($db, $ancien,$nouveau){
		$sql = "UPDATE clients SET cli_bra_id='$nouveau' WHERE cli_bra_id='$ancien'";
        if ( $result = $db->query($sql) ){
			return True;
		}
		return False;
	}
}


class Client extends Enregistrement
{
	// Constructeur
	function __construct($db)//-------------------------------------------------
	{
		$this->defaut = Array(	'cli_id' 			=> -1,
								'cli_nom' 			=> '',
								'cli_adresse' 		=> '',
								'cli_boitepostale' 	=> '',
								'cli_codepostal' 	=> '',
								'cli_ville' 		=> '',
								'cli_telephone' 	=> '',
								'cli_fax' 			=> '',
								'cli_email' 		=> '',
								'cli_tva' 			=> '',
								'cli_annee' 		=> '',
								'cli_bra_id' 		=> -1,
								'cli_zon_id' 		=> -1,
								'cli_uti_id' 		=> -1);						
		parent::__construct($db, 'clients', 'cli_id');
        $this->dates['cli_annee'] = Array('%m/%Y', '%m/%Y');
	}//--------------------------------------------------------------__construct

	public function annee()
	{
		return date("Y", strtotime($this->attribut['cli_annee']));
	}

	function Supprimer(){
		$sql = "DELETE FROM commentaires WHERE com_cli_id='{$this->id}'";
		$this->db->query($sql);
		$sql = "DELETE FROM envois WHERE env_con_id IN (SELECT con_id FROM contacts WHERE con_cli_id='{$this->id}')";
		$this->db->query($sql);
		$sql = "DELETE FROM participants WHERE par_con_id IN (SELECT con_id FROM contacts WHERE con_cli_id='{$this->id}')";
		$this->db->query($sql);
		$sql = "DELETE FROM participants WHERE par_rap_id IN (SELECT rap_id FROM rapports WHERE rap_cli_id='{$this->id}')";
		$this->db->query($sql);
		$sql = "DELETE FROM actions WHERE act_rap_id IN (SELECT rap_id FROM rapports WHERE rap_cli_id='{$this->id}')";
		$this->db->query($sql);
		$sql = "DELETE FROM rapports WHERE rap_cli_id='{$this->id}'";
		$this->db->query($sql);
		$sql = "DELETE FROM contacts WHERE con_cli_id='{$this->id}'";
		$this->db->query($sql);
		$sql = "DELETE FROM machines WHERE mac_cli_id='{$this->id}'";
		$this->db->query($sql);
		return parent::Supprimer();
	}

	// Verification des attributs via la base
	function Verification()//---------------------------------------------------
	{
		return True;
	}//-------------------------------------------------------------Verification
	
	function Traitement(&$erreur)//-----------------------------------------------------
	{
		if ( isset( $_POST['maj_sup'] ) )
		{
			if ( isset( $_POST['confirmation'] ) )
				if ( $this->Supprimer() )
				{
					$_SESSION[SES_STATUT] = LNG_STA_SUP_0;
					header('location:index.php');
				}
				else
					$_SESSION[SES_STATUT] = LNG_STA_ERREUR.LNG_STA_SUP_1;
			else
				$erreur['confirmation'] = LNG_CLI_CONFIRMATION_AIDE;
			return;
		}
		if (parent::Traitement())
			return True;
/*
		if ( isset( $_POST['maj_infos'] ) )
		{
			$this->LirePost();
			$this->Verification();
			if ( $this->VerifOk() )
				if ( $this->Ecrire() )
				{
					$_SESSION[SES_STATUT] = LNG_STA_MAJ_0;
					$id = $this->cle();
				}
				else
					$_SESSION[SES_STATUT] = LNG_STA_ERREUR.LNG_STA_MAJ_1;
		}	
*/
		if ( isset( $_POST['publier'] ) )
		{
			$com = new Commentaire($this->db, $_SESSION[SES_USER_ID], $this->cle());
			$com->Nouveau();
			$com->id = -1;
			$com->LirePost();
			$com->Verification();
			if ( $com->VerifOk() )
				if ($com->Ecrire() )
				{
					$_SESSION[SES_STATUT] = LNG_STA_MAJ_0;
					$id = $com->cle();
				}
				else
					$_SESSION[SES_STATUT] = LNG_STA_ERREUR.LNG_STA_MAJ_1;
		}
		else {
			$id_com = get_param('suppr_comm', '');
			if ($id_com){
				$com = new Commentaire($this->db, '', '');
				if ($com->Lire($id_com)){
					$com->Supprimer();
				}
			}
				
		}
			
		
	}//-------------------------------------------------------------------------

	function Ecrire(){
		$this->attribut['cli_annee'] = date('Y-m-d');
		return parent::Ecrire();
	}

	function NomFormat()
	{
		return $this->attribut['cli_nom'];
	}

	function LienPdf(){
		return "<a href='clisynthese.php?id=".$this->cle()."'><img src='img/print.png'/></a>";
	}

	function Contenu($erreur,$onglet=0, $edition=False)//-----------------------------
	{
		switch ($onglet)
		{
			case 0:
			$h4 = LNG_H4_INFORMATIONS;
			if (!$edition && $_SESSION[SES_USER_NIVEAU]>=GR_CLI_MODIF)
				$h4 .= " <a href='client.php?id={$this->cle()}&edition=1'>(modifier)</a>";
	?>
			<form method='post' action='client.php?<?="id={$this->cle()}&amp;edition=$edition"?>'>
			<?php
					dohtml_h4($h4);
	?>
			<fieldset>
			<legend>Liaisons</legend>
	<?php
					$this->ChampText('cli_nom', $edition);
					$this->ChampSelect('cli_uti_id', $edition);
					$this->ChampSelect('cli_bra_id', $edition);

	?>
			</fieldset>
			<fieldset>
			<legend>Adresse</legend>
	<?php
					$this->ChampArea('cli_adresse', $edition);
					$this->ChampText('cli_boitepostale', $edition);
					$this->ChampText('cli_codepostal', $edition);
					$this->ChampText('cli_ville', $edition);
					$this->ChampSelect('cli_zon_id', $edition);
	?>
			</fieldset>
			<fieldset>
			<legend>Coordonnées</legend>
	<?php
					$this->ChampText('cli_telephone', $edition);
					$this->ChampText('cli_fax', $edition);
					$this->ChampText('cli_email', $edition);
					$this->ChampText('cli_tva', $edition);
					$this->ChampText('cli_annee', False);
					if ($edition)
						dohtml_formbouton('labelagauche', 'Mettre à jour la base', 'maj_infos', 'Annuler les modifications');
	?>
			</fieldset>
			</form>
	<?php
		if ($this->cle() != -1 && $_SESSION[SES_USER_NIVEAU]>=GR_CLI_SUPPR){
		?>
			<form method='post' action='client.php?<?="id={$this->cle()}"?>'>
			<?php	
					dohtml_h4(LNG_H4_SUPPRESSION);
					dohtml_formcheck('labelagauche',
									'confirmation',
									LNG_CLI_CONFIRMATION,
									LNG_CLI_CONFIRMATION_AIDE,
									$erreur['confirmation']);
					dohtml_formbouton('labelagauche', 'Supprimer', 'maj_sup');
			?>
			</form>
		<?php
		}
		break;
		case 1:
		if ( droit_test(GR_COM_AJOUT) )
		{
?>
<form method='post' class='corpscomm' action='clicomm.php?<?="id={$this->cle()}&amp;edition=$edition"?>'>
	<?php
	
			$com = new Commentaire($this->db, $_SESSION[SES_USER_ID], $this->cle());
			$com->Nouveau();
			$com->ChampArea('com_texte', true, 4,10, '');					
			dohtml_formbouton('labelagauche', 'Publier', 'publier');
	?>
</form>
<?php
		}
		Commentaires::Tableau($this->db, $this->id);
		break;
		case 2:
		Liens::ConNouveau($this->id);
		Contacts::Tableau_Cli($this->db, $this->id);
		break;
		case 3:
		Liens::MacNouveau($this->id);
		Machines::Tableau_Cli($this->db, $this->id);
		break;	
		case 4:
		Liens::RapNouveau($this->id);
		Rapports::Tableau_Cli($this->db, $this->id);
		break;		}
	}//------------------------------------------------------------------Contenu
	
}
?>
